<% title "Episodes items per service in Redbee" %>

<% query = GroupInformationTable.find_by_sql(
			#"SELECT service_informations.name, schedules.service_id, count(schedules.service_id), count(DISTINCT episode_ofs.crid) FROM episode_ofs, program_informations, schedule_events, schedules, service_informations WHERE schedule_events.schedule_id=schedules.id AND schedule_events.programCrid=program_informations.crid AND schedule_events.programCrid=program_informations.crid AND program_informations.id=episode_ofs.program_information_id AND service_informations.serviceId=schedules.service_id AND program_informations.program_information_table_id=1 GROUP BY schedules.service_id") 
			"SELECT service_informations.name, schedules.service_id, count(episode_ofs.id) AS items, count(DISTINCT episode_ofs.crid)\
			AS groups FROM episode_ofs, program_informations, schedule_events, schedules, service_informations, group_informations\
			WHERE schedule_events.schedule_id=schedules.id AND schedule_events.programCrid=program_informations.crid \
			AND schedule_events.programCrid=program_informations.crid  AND program_informations.id=episode_ofs.program_information_id \
			AND service_informations.serviceId=schedules.service_id  AND program_informations.program_information_table_id=1 \
			AND group_informations.groupId=episode_ofs.crid AND group_informations.group_type='series'GROUP BY schedules.service_id\
			")
%>

<table>
  <tr>
    <th>Service name</th>
    <th>Service id</th>
    <th>Episode of items</th>
    <th>Number of groups</th>
    <th>Average episodes</th>
  </tr>
  <% for item in query %>
      <td><%= item['name'] %></td>
      <td><%= item['service_id'] %></td>
      <td><%= item['items'] %></td>
      <td><%= item['groups'] %></td>
      <td><%= item['items']/item['groups'] %></td>
      <td><%= link_to "Show details", item['service_id'] %></td>
    </tr>
  <% end %>
</table>


